﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Collections;

namespace RCA1._0
{
	public class THANHVIENDAO: DataProvider
	{
		public DataTable LayBang()
		{
			OleDbConnection cn = this.Connect();
			string strSQL = "select * from THANHVIEN";
			OleDbDataAdapter da = new OleDbDataAdapter(strSQL, cn);
			DataTable dt = new DataTable();
			da.Fill(dt);
			cn.Close();
			return dt;
		}

        #region "Lấy đối tượng"
        
        private THANHVIENDTO LayDoiTuong(String sql)
        {
            OleDbConnection cn = this.Connect();
            OleDbCommand cmd = new OleDbCommand(sql, cn);
            OleDbDataReader dr = cmd.ExecuteReader();
            THANHVIENDTO tempDTO = null;
            if (dr.Read())
            {
                tempDTO = new THANHVIENDTO();
                tempDTO.DanToc = dr["DanToc"].ToString();
                tempDTO.DiaChiThuongTru = dr["DiaChiThuongTru"].ToString();
                tempDTO.GioiTinh = dr["GioiTinh"].ToString();
                tempDTO.MaCongTy = Convert.ToInt32(dr["MaCongTy"]);
                tempDTO.IDThanhVien = Convert.ToInt32(dr["IDThanhVien"]);
                tempDTO.NgayCapCMND = DateTime.Parse(dr["NgayCapCMND"].ToString());
                tempDTO.NoiCapCMND = dr["NoiCapCMND"].ToString();
                tempDTO.NoiOHienTai = dr["NoiOHienTai"].ToString();
                tempDTO.NTNS = DateTime.Parse(dr["NTNS"].ToString());
                tempDTO.PhanTramGopVon = Convert.ToDouble(dr["PhanTramGopVon"]);
                tempDTO.QuocTich = dr["QuocTich"].ToString();
                tempDTO.SoCMND = dr["SoCMND"].ToString();
                tempDTO.SoHuuVon = dr["SoHuuVon"].ToString();
                tempDTO.TenThanhVienInHoa = dr["TenThanhVienInHoa"].ToString();
                tempDTO.TenThanhVienThuong = dr["TenThanhVienThuong"].ToString();
                tempDTO.ThoiDiemGopVon = DateTime.Parse(dr["ThoiDiemGopVon"].ToString());
                tempDTO.TongGiaTriGopVon = Convert.ToDouble(dr["TongGiaTriGopVon"]);
                tempDTO.TinhTrangThanhVien = Convert.ToInt32(dr["TinhTrangThanhVien"]);
                tempDTO.MaChucDanhThanhVien = Convert.ToInt32(dr["MaChucDanhThanhVien"]);
                tempDTO.DienThoai = dr["DienThoai"].ToString();
                tempDTO.Email = dr["Email"].ToString();
                tempDTO.Website = dr["Website"].ToString();
                tempDTO.CoDongCongTy = Convert.ToInt32(dr["CoDongCongTy"]);
            }
            cn.Close();
            return tempDTO;
        }

        #endregion


        #region "Lấy danh sách"

        private List<THANHVIENDTO> LayDSach(String sql)
        {
            OleDbConnection cn = this.Connect();
            OleDbCommand cmd = new OleDbCommand(sql, cn);
            OleDbDataReader dr = cmd.ExecuteReader();
            THANHVIENDTO tempDTO;
            List<THANHVIENDTO> dsTHANHVIEN = new List<THANHVIENDTO>();
            while (dr.Read())
            {
                tempDTO = new THANHVIENDTO();

                tempDTO.TenThanhVienInHoa = dr["TenThanhVienInHoa"].ToString();
                tempDTO.TenThanhVienThuong = dr["TenThanhVienThuong"].ToString();
                tempDTO.DanToc = dr["DanToc"].ToString();
                tempDTO.DiaChiThuongTru = dr["DiaChiThuongTru"].ToString();
                tempDTO.GioiTinh = dr["GioiTinh"].ToString();
                tempDTO.MaCongTy = Convert.ToInt32(dr["MaCongTy"]);
                tempDTO.IDThanhVien = Convert.ToInt32(dr["IDThanhVien"]);
                tempDTO.NgayCapCMND = DateTime.Parse(dr["NgayCapCMND"].ToString());
                tempDTO.NoiCapCMND = dr["NoiCapCMND"].ToString();
                tempDTO.NoiOHienTai = dr["NoiOHienTai"].ToString();
                tempDTO.NTNS = DateTime.Parse(dr["NTNS"].ToString());
                tempDTO.PhanTramGopVon = Convert.ToDouble(dr["PhanTramGopVon"]);
                tempDTO.QuocTich = dr["QuocTich"].ToString();
                tempDTO.SoCMND = dr["SoCMND"].ToString();
                tempDTO.SoHuuVon = dr["SoHuuVon"].ToString();
                tempDTO.ThoiDiemGopVon = DateTime.Parse(dr["ThoiDiemGopVon"].ToString());
                tempDTO.TongGiaTriGopVon = Convert.ToDouble(dr["TongGiaTriGopVon"]);
                tempDTO.TinhTrangThanhVien = Convert.ToInt32(dr["TinhTrangThanhVien"]);
                tempDTO.MaChucDanhThanhVien = Convert.ToInt32(dr["MaChucDanhThanhVien"]);
                tempDTO.DienThoai = dr["DienThoai"].ToString();
                tempDTO.Email = dr["Email"].ToString();
                tempDTO.Website = dr["Website"].ToString();
                tempDTO.CoDongCongTy = Convert.ToInt32(dr["CoDongCongTy"]);
                dsTHANHVIEN.Add(tempDTO);
            }
            return dsTHANHVIEN;
        }


        public List<THANHVIENDTO> LayDanhSach()
        {
            String sql = "Select * From THANHVIEN Where TinhTrangThanhVien = 1";
            return LayDSach(sql);
        }

        public List<THANHVIENDTO> LayDanhSachThanhVienTheoMaCongTy(int MaCongTy)
        {
            String sql = "Select * From THANHVIEN Where TinhTrangThanhVien = 1 and MaCongTy = " + MaCongTy;
            return LayDSach(sql);
        }

        #endregion


        #region "Lấy danh sách giới tính"

        private List<THANHVIENDTO> LayDSachGioiTinh(String sql)
        {
            OleDbConnection cn = this.Connect();
            OleDbCommand cmd = new OleDbCommand(sql, cn);
            OleDbDataReader dr = cmd.ExecuteReader();
            THANHVIENDTO tempDTO;
            List<THANHVIENDTO> dsTHANHVIEN = new List<THANHVIENDTO>();
            while (dr.Read())
            {
                tempDTO = new THANHVIENDTO();
                                
                tempDTO.GioiTinh = dr["GioiTinh"].ToString();             
                               
                dsTHANHVIEN.Add(tempDTO);
            }
            return dsTHANHVIEN;
        }

        

        public List<THANHVIENDTO> LayDanhSachGioiTinh()
        {
            String sql = "Select GioiTinh From THANHVIEN ";
            return LayDSachGioiTinh(sql);
        }

        public List<THANHVIENDTO> LayLoaiGioiTinh()
        {
            String sql = "Select distinct GioiTinh From THANHVIEN ";
            return LayDSachGioiTinh(sql);
        }


        #endregion


        #region "Tìm kiếm"
        public THANHVIENDTO TimTheoMa(int MaThanhVien)
		{

            string strSQL = "select * from THANHVIEN Where TinhTrangThanhVien=1 and IDThanhVien=" + MaThanhVien.ToString();
		    return LayDoiTuong(strSQL);
		}
        #endregion


        #region "Thêm"

        public void Them(THANHVIENDTO tempDTO)
		{
			OleDbConnection cn = this.Connect();
            string strSQL = "insert into THANHVIEN(DanToc, DiaChiThuongTru, GioiTinh, MaCongTy, NgayCapCMND, NoiCapCMND, NoiOHienTai, NTNS, PhanTramGopVon, QuocTich, SoCMND, SoHuuVon, TenThanhVienInHoa, TenThanhVienThuong, ThoiDiemGopVon, TongGiaTriGopVon, TinhTrangThanhVien, MaChucDanhThanhVien, DienThoai, Email, Website, CoDongCongTy) values (@DanToc, @DiaChiThuongTru, @GioiTinh, @MaCongTy, @NgayCapCMND, @NoiCapCMND, @NoiOHienTai, @NTNS, @PhanTramGopVon, @QuocTich, @SoCMND, @SoHuuVon, @TenThanhVienInHoa, @TenThanhVienThuong, @ThoiDiemGopVon, @TongGiaTriGopVon, @TinhTrangThanhVien, @MaChucDanhThanhVien, @DienThoai, @Email, @Website, @CoDongCongTy)";
			OleDbCommand cmd = new OleDbCommand(strSQL, cn);
			cmd.Parameters.Add("@DanToc", OleDbType.LongVarWChar).Value = tempDTO.DanToc;
			cmd.Parameters.Add("@DiaChiThuongTru", OleDbType.LongVarWChar).Value = tempDTO.DiaChiThuongTru;
			cmd.Parameters.Add("@GioiTinh", OleDbType.LongVarWChar).Value = tempDTO.GioiTinh;
            cmd.Parameters.Add("@MaCongTy", OleDbType.Integer).Value = tempDTO.MaCongTy;
			cmd.Parameters.Add("@NgayCapCMND", OleDbType.DBDate).Value = tempDTO.NgayCapCMND;
			cmd.Parameters.Add("@NoiCapCMND", OleDbType.LongVarWChar).Value = tempDTO.NoiCapCMND;
			cmd.Parameters.Add("@NoiOHienTai", OleDbType.LongVarWChar).Value = tempDTO.NoiOHienTai;
			cmd.Parameters.Add("@NTNS", OleDbType.DBDate).Value = tempDTO.NTNS;
			cmd.Parameters.Add("@PhanTramGopVon", OleDbType.Double).Value = tempDTO.PhanTramGopVon;
			cmd.Parameters.Add("@QuocTich", OleDbType.LongVarWChar).Value = tempDTO.QuocTich;
			cmd.Parameters.Add("@SoCMND", OleDbType.LongVarWChar).Value = tempDTO.SoCMND;
			cmd.Parameters.Add("@SoHuuVon", OleDbType.LongVarWChar).Value = tempDTO.SoHuuVon;
			cmd.Parameters.Add("@TenThanhVienInHoa", OleDbType.LongVarWChar).Value = tempDTO.TenThanhVienInHoa;
			cmd.Parameters.Add("@TenThanhVienThuong", OleDbType.LongVarWChar).Value = tempDTO.TenThanhVienThuong;
			cmd.Parameters.Add("@ThoiDiemGopVon", OleDbType.DBDate).Value = tempDTO.ThoiDiemGopVon;
			cmd.Parameters.Add("@TongGiaTriGopVon", OleDbType.Double).Value = tempDTO.TongGiaTriGopVon;
            cmd.Parameters.Add("@TinhTrangThanhVien", OleDbType.LongVarWChar).Value = tempDTO.TinhTrangThanhVien = 1;

            cmd.Parameters.Add("@MaChucDanhThanhVien", OleDbType.Integer).Value = tempDTO.MaChucDanhThanhVien;
            cmd.Parameters.Add("@DienThoai", OleDbType.LongVarWChar).Value = tempDTO.DienThoai;
            cmd.Parameters.Add("@Email", OleDbType.LongVarWChar).Value = tempDTO.Email;
            cmd.Parameters.Add("@Website", OleDbType.LongVarWChar).Value = tempDTO.Website;
            cmd.Parameters.Add("@CoDongCongTy", OleDbType.Integer).Value = tempDTO.CoDongCongTy;
			cmd.ExecuteNonQuery();
			cn.Close();
		}

        #endregion


        #region "Xóa"

        public void XoaTheoMaCongTy(int MaCongTy)
        {
            OleDbConnection cn = this.Connect();
            string strSQL = "update THANHVIEN set TinhTrangThanhvien = 2 where MaCongTy =" + MaCongTy;
            OleDbCommand cmd = new OleDbCommand(strSQL, cn);

            cmd.ExecuteNonQuery();
            cn.Close();
        }
        //public void Xoa(int IDThanhVien)
        //{
        //    OleDbConnection cn = this.Connect();
        //    string strSQL = "delete from THANHVIEN Where IDThanhVien = "+IDThanhVien.ToString();
        //    OleDbCommand cmd = new OleDbCommand(strSQL, cn);
        //    cmd.ExecuteNonQuery();
        //    cn.Close();
        //}

        /// 
        public void Xoa(int MaThanhVien)
        {
            OleDbConnection cn = this.Connect();
            string strSQL = "update THANHVIEN set TinhTrangThanhvien = 2 where IDThanhVien = " + MaThanhVien.ToString();
            OleDbCommand cmd = new OleDbCommand(strSQL, cn);

            cmd.ExecuteNonQuery();
            cn.Close();
        }
        
        #endregion

  
        #region "Sửa"

        public void Sua(THANHVIENDTO tempDTO)
		{
			OleDbConnection cn = this.Connect();
            string strSQL = "update THANHVIEN set DanToc = @DanToc, DiaChiThuongTru = @DiaChiThuongTru, GioiTinh = @GioiTinh, MaCongTy = @MaCongTy, NgayCapCMND = @NgayCapCMND, NoiCapCMND = @NoiCapCMND, NoiOHienTai = @NoiOHienTai, NTNS = @NTNS, PhanTramGopVon = @PhanTramGopVon, QuocTich = @QuocTich, SoCMND = @SoCMND, SoHuuVon = @SoHuuVon, TenThanhVienInHoa = @TenThanhVienInHoa, TenThanhVienThuong = @TenThanhVienThuong, ThoiDiemGopVon = @ThoiDiemGopVon, TongGiaTriGopVon = @TongGiaTriGopVon, TinhTrangThanhVien = @TinhTrangThanhVien, MaChucDanhThanhVien = @MaChucDanhThanhVien, DienThoai = @DienThoai, Email = @Email, Website = @Website, CoDongCongTy = @CoDongCongTy where IDThanhVien = @IDThanhVien";
			OleDbCommand cmd = new OleDbCommand(strSQL, cn);
			cmd.Parameters.Add("@DanToc", OleDbType.LongVarWChar).Value = tempDTO.DanToc;
			cmd.Parameters.Add("@DiaChiThuongTru", OleDbType.LongVarWChar).Value = tempDTO.DiaChiThuongTru;
			cmd.Parameters.Add("@GioiTinh", OleDbType.LongVarWChar).Value = tempDTO.GioiTinh;
            cmd.Parameters.Add("@MaCongTy", OleDbType.Integer).Value = tempDTO.MaCongTy;
			cmd.Parameters.Add("@NgayCapCMND", OleDbType.DBDate).Value = tempDTO.NgayCapCMND;
			cmd.Parameters.Add("@NoiCapCMND", OleDbType.LongVarWChar).Value = tempDTO.NoiCapCMND;
			cmd.Parameters.Add("@NoiOHienTai", OleDbType.LongVarWChar).Value = tempDTO.NoiOHienTai;
			cmd.Parameters.Add("@NTNS", OleDbType.DBDate).Value = tempDTO.NTNS;
			cmd.Parameters.Add("@PhanTramGopVon", OleDbType.Double).Value = tempDTO.PhanTramGopVon;
			cmd.Parameters.Add("@QuocTich", OleDbType.LongVarWChar).Value = tempDTO.QuocTich;
			cmd.Parameters.Add("@SoCMND", OleDbType.LongVarWChar).Value = tempDTO.SoCMND;
			cmd.Parameters.Add("@SoHuuVon", OleDbType.LongVarWChar).Value = tempDTO.SoHuuVon;
			cmd.Parameters.Add("@TenThanhVienInHoa", OleDbType.LongVarWChar).Value = tempDTO.TenThanhVienInHoa;
			cmd.Parameters.Add("@TenThanhVienThuong", OleDbType.LongVarWChar).Value = tempDTO.TenThanhVienThuong;
			cmd.Parameters.Add("@ThoiDiemGopVon", OleDbType.DBDate).Value = tempDTO.ThoiDiemGopVon;
			cmd.Parameters.Add("@TongGiaTriGopVon", OleDbType.Double).Value = tempDTO.TongGiaTriGopVon;
            cmd.Parameters.Add("@TinhTrangThanhVien", OleDbType.LongVarWChar).Value = tempDTO.TinhTrangThanhVien;
			cmd.Parameters.Add("@IDThanhVien", OleDbType.Integer).Value = tempDTO.IDThanhVien;

            cmd.Parameters.Add("@MaChucDanhThanhVien", OleDbType.Integer).Value = tempDTO.MaChucDanhThanhVien;
            cmd.Parameters.Add("@DienThoai", OleDbType.LongVarWChar).Value = tempDTO.DienThoai;
            cmd.Parameters.Add("@Email", OleDbType.LongVarWChar).Value = tempDTO.Email;
            cmd.Parameters.Add("@Website", OleDbType.LongVarWChar).Value = tempDTO.Website;
            cmd.Parameters.Add("@CoDongCongTy", OleDbType.Integer).Value = tempDTO.CoDongCongTy;
			cmd.ExecuteNonQuery();
			cn.Close();
        }

        #endregion

    }
}